package com.titan.core.utils.dbinfo.dialect.impl;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.titan.core.utils.data.FormatUtils;
import com.titan.core.utils.data.StringUtils;
import com.titan.core.utils.db.DBUtils;
import com.titan.core.utils.db.RecordSet;
import com.titan.core.utils.dbinfo.TableInfoUtil;
import com.titan.core.utils.dbinfo.bean.ColumnBean;
import com.titan.core.utils.dbinfo.dialect.IDBDialect;
import com.titan.core.utils.file.ClassUtils;
import com.titan.core.utils.object.TableColumnDescriptor;
import com.titan.core.utils.object.TableDescriptor;

/**
 * @author 周成功
 * @email 86898837@qq.com
 * @date 2020年02月14日
 */
public class MySQLDialect implements IDBDialect {
    private final static String DRIVER_NAME = "com.mysql.jdbc.Driver";
    static {
        try {
            if(ClassUtils.isExistClass(DRIVER_NAME)) {
                Driver driver = (Driver) Class.forName(DRIVER_NAME).newInstance();
                DriverManager.registerDriver(driver);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    
    @Override
    public String getDriverName() {
        return DRIVER_NAME;
    }

    @Override
    public String getUrl(String ip, int port, String serviceName) {
        return StringUtils.append("jdbc:mysql://", ip, ":", port, "/", serviceName, "?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true");
    }

    @Override
    public Connection getConnection(String ip, int port, String serviceName, String userid, String password) throws SQLException {
        String url = getUrl(ip, port, serviceName);
        return getConnection(url, userid, password);
    }

    @Override
    public Connection getConnection(String url, String userid, String password) throws SQLException {
        return DriverManager.getConnection(url, userid, password);
    }
    
    @Override
    public String identifier(String key) {
        return new StringBuilder(key.length()+2).append('`').append(key).append('`').toString();
    }

    @Override
    public String sqlSysdate() {
        return "sysdate()";
    }

    @Override
    public void sqlPage(StringBuilder sql, int pageIndex, int pageSize) {
        sql.append(" limit ").append((pageIndex-1)*pageSize).append(",").append(pageSize);
    }

    @Override
    public List<String> getTables(Connection conn, String schema) throws Exception {
        String sql = "select table_name tname, table_comment from information_schema.tables where upper(table_schema) = upper('"+schema+"') and upper(table_type)='BASE TABLE'";
        List<String> result =new ArrayList<String>();
        Statement stmt = null;
        ResultSet rest = null;
        try {
            stmt = conn.createStatement();
            rest = stmt.executeQuery(sql);
            while(rest.next()) {
                String name = rest.getString(1);
                result.add(name);
            }
            return result;
        } finally {
            DBUtils.close(rest);
            DBUtils.close(stmt);
        }
    }
    
    @Override
    public TableColumnDescriptor[] getColumn(Connection conn, String schema, String tablename) throws Exception {
        StringBuffer sql = new StringBuffer(512);
        sql.append("select column_name");
        sql.append("       ,data_type");
        sql.append("       ,character_maximum_length");
        sql.append("       ,numeric_precision");
        sql.append("       ,numeric_scale");
        sql.append("       ,is_nullable");
        sql.append("       ,column_comment");
        sql.append("  from information_schema.columns");
        sql.append(" where upper(table_name)=upper(?)");
        if(StringUtils.isNotEmpty(schema)) {
            sql.append(" and upper(table_schema) = upper('"+schema+"')");
        }
        sql.append(" order by ordinal_position");
        RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql.toString(), new String[]{tablename});
        TableColumnDescriptor[] result =new TableColumnDescriptor[rest.getRowCount()];
        for (int row = 0; row < rest.getRowCount(); row++) {
            result[row] = new TableColumnDescriptor();
            result[row].setNameEnglish(rest.getString(row + 1, "column_name"));
            result[row].setSqlType(rest.getString(row + 1, "data_type"));
            result[row].setDataLength(rest.getInt(row + 1, "character_maximum_length"));
            result[row].setDataPrecision(rest.getInt(row + 1, "numeric_precision"));
            result[row].setDataScale(rest.getInt(row + 1, "numeric_scale"));
            result[row].setNullable(FormatUtils.toBoolean(rest.getString(row + 1, "is_nullable")));
            result[row].setComments(rest.getString(row + 1, "column_comment"));
            
            String[] comments = TableInfoUtil.analyze(result[row].getComments(), result[row].getNameEnglish());
            result[row].setNameChina(comments[0]);
            result[row].setComments(comments[1]);
        }
        return result;
    }

    @Override
    public TableDescriptor getTable(Connection conn, String schema, String tablename) throws Exception {
        // 获得表信息
        StringBuffer sql = new StringBuffer(256);
        sql.append("select table_type, table_comment");
        sql.append("  from information_schema.tables");
        sql.append(" where upper(table_name)=upper(?)");
        if(StringUtils.isNotEmpty(schema)) {
            sql.append(" and upper(table_schema) = upper('"+schema+"')");
        }
        RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql.toString(), new String[]{tablename});
        if (rest.moveNext()) {
            TableDescriptor table = new TableDescriptor();
            table.setNameEnglish(tablename);
            table.setComments(rest.getString("table_comment"));
            table.setType(rest.getString("table_type"));
            String[] comments = TableInfoUtil.analyze(table.getComments(), table.getNameEnglish());
            table.setNameChina(comments[0]);
            table.setComments(comments[1]);
            
            table.setColumns(this.getColumn(conn, schema, tablename));
            table.setPrimaryKey(this.getPrimaryKey(conn, schema, tablename));
            table.setUniqueIndex(this.getUniqueIndex(conn, schema, tablename));
            return table;
        } else {
            return null;
        }
    }

    @Override
    public String[] getPrimaryKey(Connection conn, String schema, String tablename) throws Exception {
        StringBuffer sql = new StringBuffer(256);
        sql.append("select column_name");
        sql.append("  from information_schema.key_column_usage");
        sql.append(" where upper(constraint_name)='PRIMARY'");
        sql.append("   and upper(table_name)=upper(?)");
        if(StringUtils.isNotEmpty(schema)) {
            sql.append(" and upper(table_schema) = upper('"+schema+"')");
        }
        sql.append(" order by ordinal_position");
        RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql.toString(), new String[]{tablename});
        String[] primaryKey = new String[rest.getRowCount()];
        for (int i = 0; i < rest.getRowCount(); i++) {
            primaryKey[i] = rest.getString(i + 1, "column_name");
        }
        return primaryKey;
    }

    @Override
    public String[][] getUniqueIndex(Connection conn, String schema, String tablename) throws Exception {
        StringBuffer sql = new StringBuffer(256);
        sql.append("select distinct constraint_name");
        sql.append("  from information_schema.key_column_usage");
        sql.append(" where upper(constraint_name)!='PRIMARY'");
        sql.append("   and upper(table_name)=upper(?)");
        if(StringUtils.isNotEmpty(schema)) {
            sql.append(" and upper(table_schema) = upper('"+schema+"')");
        }
        String[] values = DBUtils.getValues(conn, sql.toString(), new String[]{tablename});
        String[][] items = new String[values.length][];
        for (int i = 0; i < items.length; i++) {
            sql = new StringBuffer(256);
            sql.append("select constraint_name,column_name");
            sql.append("  from information_schema.key_column_usage");
            sql.append(" where upper(constraint_name)!='PRIMARY'");
            sql.append("   and upper(table_name)=upper(?)");
            sql.append("   and upper(constraint_name)=upper(?)");
            if(StringUtils.isNotEmpty(schema)) {
                sql.append(" and upper(table_schema) = upper('"+schema+"')");
            }
            sql.append(" order by ordinal_position");
            RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql.toString(), new String[]{tablename, values[i]});
            String[] primaryKey = new String[rest.getRowCount()];
            for (int j = 0; j < rest.getRowCount(); j++) {
                primaryKey[j] = rest.getString(j + 1, "column_name");
            }
            items[i] = primaryKey;
        }
        return items;
    }
}
